package com.jlcindia.hibernate;

import java.util.List;

import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;

import com.jlcindia.util.HibernateUtil;

public class Client1 {
	public static void main(String args[]) {
		Transaction tx = null;
		try {
			SessionFactory sf = HibernateUtil.getSessionFactory();
			Session session = sf.openSession();
			tx = session.beginTransaction();

			// 1. All Contacts
			String sql1 = "select {cts.*} from contacts cts";
			SQLQuery sq1 = session.createSQLQuery(sql1);
			List<Contact> clist1 = sq1.addEntity("cts", Contact.class).list();
			for (Contact c : clist1) {
				System.out.println(c.getContactId() + "\t" + c.getFirstName()
						+ "\t" + c.getLastName() + "\t" + c.getEmail() + "\t"
						+ c.getPhone() + "\t" + c.getStatus());
			}

			// 2. Contacts by Email
			String sql2 = "select {cts.*} from contacts cts where cts.email=?";
			SQLQuery sq2 = session.createSQLQuery(sql2);
			sq2.setString(0, "sri@jlc");
			List<Contact> clist2 = sq2.addEntity("cts", Contact.class).list();
			for (Contact c : clist2) {
				System.out.println(c.getContactId() + "\t" + c.getFirstName()
						+ "\t" + c.getLastName() + "\t" + c.getEmail() + "\t"
						+ c.getPhone() + "\t" + c.getStatus());
			}

			// 2. Contacts by Status
			String sql3 = "select {cts.*} from contacts cts where cts.status= :sts";
			SQLQuery sq3 = session.createSQLQuery(sql3);
			sq3.setString("sts", "Active");
			List<Contact> clist3 = sq3.addEntity("cts", Contact.class).list();
			for (Contact c : clist3) {
				System.out.println(c.getContactId() + "\t" + c.getFirstName()
						+ "\t" + c.getLastName() + "\t" + c.getEmail() + "\t"
						+ c.getPhone() + "\t" + c.getStatus());
			}

			tx.commit();
			session.close();

		} catch (Exception e) {
			if (tx != null) {
				tx.rollback();
			}
			e.printStackTrace();
		}
	}
}
